<pre>
<?php
	require_once('config.php');

	$weekPastDate = date('Y-m-d',strtotime("-7 day")); //Date before 7 days form current date
	$weekPastDate = '2013-04-01';
	$currentdate = date('Y-m-d'); //Current Date

	/*Total Active Jobs POSTED withing last seven days*/
	$activeJobSql = "SELECT j.id as job_id,j.title,j.trade_id ,a.`area`,a.longitude,a.latitude,t.trade
					FROM jobs j
					JOIN trades t ON j.trade_id = t.id 
					JOIN areas a ON j.area_id = a.id 
					JOIN addresses adr ON j.address_id = adr.id 
					WHERE j.status = 'Open' AND j.job_active = '1' AND j.deleted <> '1' 
					AND j.date_added BETWEEN '".$weekPastDate."' AND '".$currentdate."'
					ORDER BY j.date_added";

	$aciveJobs = $DBOBJ->select($activeJobSql);
	foreach($aciveJobs as $_jobs) {
		$jobsArray[$_jobs['job_id']] = $_jobs;
	}
	
	$tradesIdArray = Array();
	foreach($aciveJobs as $_val) {
		$tradesIdArray[$_val['trade_id']] = $_val['trade_id'];
	}
	
	$tradeIds = '';
	if ( count($tradesIdArray) > 0 ) {
		$tradeIds = implode(",",$tradesIdArray);
	}
	
	if ( $tradeIds != '' ) {
			/*All tradesman to whom mail should be sent */
			$tradesmanSql = "SELECT t.id,t.user_id,t.address_id,tt.trade_id,s.name,s.radius,u.firstname,u.surname,u.email,a.longitude,a.latitude
							FROM  tradesman_to_trade tt
							JOIN tradesman t ON t.id = tt.tradesman_id
							JOIN subscriptions s ON t.subscription_id = s.id
							JOIN users u ON t.user_id = u.id 
							JOIN addresses a ON t.address_id = a.id
							WHERE tt.trade_id  IN (".$tradeIds.") AND t.active = 1 AND u.banned = 0 AND u.newsletter = 1 AND u.tradesman = 1
							GROUP BY  tt.tradesman_id limit 10";
			$tradesManArray = $DBOBJ->select($tradesmanSql);
			$newsletter_content = '';
			foreach($tradesManArray as $_tradeMan) {
				foreach($jobsArray as $_job) {
					if ($_tradeMan['trade_id'] == $_job['trade_id']) {
						$distance = '';
						$distance = getDistanceBetweenPoints($_tradeMan['latitude'],$_tradeMan['longitude'], $_job['latitude'],$_job['longitude']);
						//Condition to send mail as per subscription area
						if ( $distance <= $_tradeMan['radius']  ) {
								//Mail Logic Over Here
								//Job Links Building
								$newsletter_content = '';
								$link = build_job_link($_job['trade'],$_job['area'],$_job['job_id']);
								$newsletter_content.='<a href="http://www.iwantatradesman.co.uk'.$link .'" style="color: #002D4E; text-decoration: none;"><b>'.$_job['title'].'</b></a> - '.$_job['area'].'<br />';
						}
					}
				}
			}
	}
	
	
/* This Function is used to calculate the distance between towo co-ordinate of latitude and longitude */
    function getDistanceBetweenPoints($latitude1, $longitude1, $latitude2, $longitude2, $unit = 'Mi') {
        $theta = $longitude1 - $longitude2;
        $distance = (sin(deg2rad($latitude1)) * sin(deg2rad($latitude2))) + (cos(deg2rad($latitude1)) * cos(deg2rad($latitude2)) * cos(deg2rad($theta)));
        $distance = acos($distance);
        $distance = rad2deg($distance);
        $distance = $distance * 60 * 1.1515;
        switch ($unit) {
            case 'Mi':
                break;
            case 'Km' :
                $distance = $distance * 1.609344;
                break;
        }
        $distance = round($distance, 2);
		return $distance;
    }	
	
	/*Function Copied from the buildLinks.php*/	
	function clean_file_name($Raw){
		$Raw = trim(strtolower($Raw));
		$RemoveChars  = array( "([\40])" , "([^a-zA-Z0-9-])", "(-{2,})" );
		$ReplaceWith = array("-", "", "-");
		return preg_replace($RemoveChars, $ReplaceWith, $Raw);
	} 

	/*Function Copied from the buildLinks.php*/	
	function build_job_link($trade,$area,$id){

	$link = '/job/'.clean_file_name($trade).'/'.clean_file_name($area).'/'.$id;

	return $link;

	}
	
	
exit;
?>